﻿/**
 * @copyright Copyright 2009 Structured Solutions
 * @license http://www.codeplex.com/ShippingAgent/license Microsoft Public License (Ms-PL)
 * @version 3.3
 */

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Serialization;
using StoreFront.BusinessRule;
using StoreFront.BusinessRule.Management;
using StoreFront.BusinessRule.Orders;
using StoreFront.DataAccess;
using StoreFront.SystemBase;
using StoreFrontSecurity;

namespace StructuredSolutions.ShippingAgent
{
    [WebService(Namespace = "http://www.codeplex.com/ShippingAgent",
        Description = "Shipping Agent for StoreFront 7.2")]
    public class ShippingAgentImpl : ShippingAgentSoap
    {
        public const string VERSION = "3.3.0";

        #region WebMethod Implementations

        /// <summary>
        /// Return orders placed since pageKey. If pageKey is null or an empty
        /// string, then return all orders from today.
        /// </summary>
        /// <param name="pageKey">The last order number in the previous results. May be
        /// null or an empty string if this is the first time this method is called.</param>
        /// <param name="maxCount">The maximum number of orders to return.</param>
        /// <returns>Up to maxCount OrderResults, sorted by order number; or an empty list or null 
        /// if there are no new orders.</returns>
        /// <remarks>
        /// <para>If maxCount orders are returned, the Shipper client will call this method again with the 
        /// same ranges and with pageKey set to the last order number in the previous result. In this
        /// case, use pageKey to calculate an appropriate new startOrderNumber (for example,
        /// startOrderNumber = pageKey + 1).</para>
        /// <para>Return a <see cref="SoapException"/> if the parameters are invalid.</para>
        /// </remarks>
        [WebMethod(Description = "Retrieve orders placed since pageKey.")]
        public override OrderResultsPage GetNewOrders(string pageKey, int maxCount)
        {
            try
            {
                return GetNewOrdersFromStore(pageKey, maxCount);
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve new orders.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Return one order.
        /// </summary>
        /// <param name="orderId">The ID of the order.</param>
        /// <returns>The OrderResult.</returns>
        /// <exception cref="SoapException">If the orderID is not valid (such as blank or null).</exception>
        [WebMethod(Description = "Retrieve one order.")]
        public override OrderResult GetOrder(string orderId)
        {
            try
            {
                if (string.IsNullOrEmpty(orderId))
                    throw new ApplicationException("Invalid order ID.");
                return GetOrderFromStore(orderId);
            }
            catch (ApplicationException ex)
            {
                var result = new OrderResult();
                result.Success = false;
                result.Message = ex.Message;
                return result;
            }
            catch (Exception ex)
            {
                var result = new OrderResult();
                result.Success = false;
                result.Message = ex.ToString();
                return result;
            }
        }

        /// <summary>
        /// Return the current inventory for all items in one order.
        /// </summary>
        /// <param name="orderId">The ID of the order.</param>
        /// <returns>A list of item Inventory objects or null if inventory is not tracked.</returns>
        /// <exception cref="SoapException">If the orderID is not valid (such as blank or null).</exception>
        [WebMethod(Description = "Retrieve the current inventory for all items in one order.")]
        public override InventoryResult GetOrderInventory(string orderId)
        {
            try
            {
                if (string.IsNullOrEmpty(orderId))
                    throw new ApplicationException("Invalid order ID.");
                return GetOrderInventoryFromStore(orderId);
            }
            catch (ApplicationException ex)
            {
                var result = new InventoryResult();
                result.Success = false;
                result.Message = ex.Message;
                return result;
            }
            catch (Exception ex)
            {
                var result = new InventoryResult();
                result.Success = false;
                result.Message = ex.ToString();
                return result;
            }
        }

        /// <summary>
        /// Return all the orders that fall on and within the specified ranges.
        /// </summary>
        /// <param name="startOrderNumber">The starting order number; or null or an empty string to start at the first order number in the store.</param>
        /// <param name="endOrderNumber">The ending order number; or null or an empty string to end with the last order number in the store.</param>
        /// <param name="startOrderDate">The starting order date. If no start date is specified, start from the earliest order.</param>
        /// <param name="startOrderDateSpecified">True if startOrderDate is specified, false otherwise.</param>
        /// <param name="endOrderDate">The ending order date. If no end date is specified, end with the most recent order.</param>
        /// <param name="endOrderDateSpecified">True if endOrderDate is specified, false otherwise.</param>
        /// <param name="pageKey">The last order number from the previous result; or null or an empty string to ignore this parameter.</param>
        /// <param name="maxCount">The maximum number of orders to return.</param>
        /// <returns>Up to maxCount Order's, sorted by order number; or an empty list or null 
        /// if there are no orders that fall on and within the specified ranges.</returns>
        /// <remarks>If maxCount orders are returned, the Shipper client will call this method again with the 
        /// same ranges and with lastOrderNumber set to the last order number in the previous result. In this
        /// case, use lastOrderNumber to calculate an appropriate new startOrderNumber (for example,
        /// startOrderNumber = lastOrderNumber + 1).
        /// </remarks>
        [WebMethod(Description = "Return the list of orders that fall on and within the specified ranges.")]
        public override OrderResultsPage GetOrdersByRange(string startOrderNumber, string endOrderNumber, DateTime startOrderDate, bool startOrderDateSpecified, DateTime endOrderDate, bool endOrderDateSpecified, string pageKey, int maxCount)
        {
            try
            {
                var nullableStartDate = ShippingAgentHelper.ConvertWebServiceNullable(startOrderDate, startOrderDateSpecified);
                var nullableEndDate = ShippingAgentHelper.ConvertWebServiceNullable(endOrderDate, endOrderDateSpecified);
                return GetOrdersByRangeFromStore(startOrderNumber, endOrderNumber, nullableStartDate, nullableEndDate, pageKey, maxCount);
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve orders by range.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Return one specific package.
        /// </summary>
        /// <param name="orderId">The ID of the order.</param>
        /// <param name="packageId">The ID of the package.</param>
        /// <returns>A PackageResult.</returns>
        /// <exception cref="SoapException">If the orderId or packageId is not valid (such as blank or null).</exception>
        [WebMethod(Description = "Retrieve one package.")]
        public override PackageResult GetPackage(string orderId, string packageId)
        {
            try
            {
                if (string.IsNullOrEmpty(orderId))
                    throw new ApplicationException("Invalid order ID.");
                if (string.IsNullOrEmpty(packageId))
                    throw new ApplicationException("Invalid package ID.");
                return GetPackageFromStore(orderId, packageId);
            }
            catch (ApplicationException ex)
            {
                var result = new PackageResult();
                result.Success = false;
                result.Message = ex.Message;
                return result;
            }
            catch (Exception ex)
            {
                var result = new PackageResult();
                result.Success = false;
                result.Message = ex.ToString();
                return result;
            }
        }

        /// <summary>
        /// Returns the complete list of tracking providers supported by the store. The
        /// Shipper client may assign any tracking provider to a package, regardless of
        /// how the shipping cost was initially calculated.
        /// </summary>
        /// <returns>The list of TrackingProvider's.</returns>
        [WebMethod(Description = "Retrieve the list of package tracking providers.")]
        [return: XmlArrayItem("TrackingProviders", IsNullable = false)]
        public override TrackingProvider[] GetTrackingProviders()
        {
            try
            {
                return GetTrackingProvidersFromStore().ToArray();
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve tracking providers.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Return the ShippingAgent.wsdl implemented by this ShippingAgent.
        /// </summary>
        /// <returns>A string value that represents the version using the format
        /// Major.Minor.Revision. For example, "3.0.0".</returns>
        [WebMethod(Description = "Retrieve the WSDL interface version implemented by this Shipping Agent.")]
        public override string GetVersion()
        {
            return VERSION;
        }

        /// <summary>
        /// Record package information.
        /// </summary>
        /// <param name="update">The package information to record.</param>
        /// <returns>If the update is successful, the UpdateResult contains a fresh
        /// copy of the Order that contains the package. If the update is not
        /// successful, the UpdateResult contains the reason why the update failed.</returns>
        /// <remarks>The Shipper client may not include all Update elements. null
        /// elements should be ignored. If the ShipDate is included, then the package
        /// should be marked shipped.</remarks>
        [WebMethod(Description = "Record package information.")]
        public override UpdateResult UpdatePackage(Update update)
        {
            try
            {
                if (update == null)
                    return null;
                if (string.IsNullOrEmpty(update.OrderId))
                    throw new ApplicationException("Invalid order ID.");
                if (string.IsNullOrEmpty(update.PackageId))
                    throw new ApplicationException("Invalid package ID.");
                return UpdatePackageInStore(update);
            }
            catch (ApplicationException ex)
            {
                var result = new UpdateResult();
                result.Success = false;
                result.Message = ex.Message;
                return result;
            }
            catch (Exception ex)
            {
                var result = new UpdateResult();
                result.Success = false;
                result.Message = ex.ToString();
                return result;
            }
        }

        #endregion

		#region Platform Specific Methods

        protected static OrderResultsPage GetNewOrdersFromStore(string pageKey, int maxCount)
        {
            var orders = new List<OrderResult>();

            long sfLastOrderNumber = 0;
            if (!string.IsNullOrEmpty(pageKey))
            {
                if (!long.TryParse(pageKey, out sfLastOrderNumber))
                    throw new ApplicationException("Invalid paging key.");
            }

            int recordsCount;
            using (var sfOrderAccess = new COrderAccess())
            {
                var whereSql = "FROM " + sfOrderAccess.TableName + " WHERE (OrderNumber IS NOT NULL) AND (Void <> 1)";
                if (!string.IsNullOrEmpty(pageKey))
                {
                    whereSql += " AND (OrderNumber > " + sfLastOrderNumber + ")";
                }
                else
                {
                    // No pageKey means this is the first call. Return today's orders.
                    switch (StoreFrontConfiguration.DataBase)
                    {
                        case DatabaseType.Access:
                            whereSql += " AND (CDate(DateOrdered) >= #" + DateTime.Today.ToShortDateString() + "#)";
                            break;
                        case DatabaseType.SqlServer:
                            whereSql += " AND (CONVERT(DATETIME, DateOrdered) >= '" + DateTime.Today.ToShortDateString() + "')";
                            break;
                    }
                }

                var selectSql = "SELECT TOP " + maxCount + " OrderNumber " + whereSql;
                using (var sfOrders = sfOrderAccess.SQLSelect(selectSql + " ORDER BY OrderNumber", null, 0, 0))
                {
                    foreach (DataRow row in sfOrders.Tables[0].Rows)
                    {
                        orders.Add(BuildOrderResult(Convert.ToInt64(row["OrderNumber"])));
                    }
                }

                var countSql = "SELECT COUNT(*) " + whereSql;
                using (var sfCount = sfOrderAccess.SQLSelect(countSql, null, 0, 0))
                {
                    recordsCount = Convert.ToInt32(sfCount.Tables[0].Rows[0][0]);
                }
            }

            if (orders.Count == 0)
                return null;

            var result = new OrderResultsPage();
            result.Key = orders[orders.Count - 1].Order.OrderNumber;
            result.OrderResults = orders.ToArray();
            result.Remaining = recordsCount - orders.Count;
            return result;
        }

        /// <summary>
        /// Return one Order from the store.
        /// </summary>
        /// <param name="orderId">The OrderNumber of the StoreFront COrder object.</param>
        /// <returns>The Order corresponding to the StoreFront COrder.</returns>
        protected static OrderResult GetOrderFromStore(string orderId)
        {
            long sfOrderNumber;
            if (!long.TryParse(orderId, out sfOrderNumber))
                throw new ApplicationException("Invalid order ID.");

            return BuildOrderResult(sfOrderNumber);
        }

        protected static InventoryResult GetOrderInventoryFromStore(string orderId)
        {
            long sfOrderNumber;
            if (!long.TryParse(orderId, out sfOrderNumber))
                throw new ApplicationException("Invalid order ID.");

            using (var management = new CManagement(sfOrderNumber))
            {
                var summaries = management.OrderSummary;
                if (summaries.Count == 0)
                    throw new ApplicationException("Invalid order ID.");

                return BuildInventoryResult((COrderManagmentSummary)summaries[0]);
            }
        }

        protected static OrderResultsPage GetOrdersByRangeFromStore(string startOrderNumber, string endOrderNumber, DateTime? startOrderDate,
                                                                 DateTime? endOrderDate, string pageKey, int maxCount)
        {
            var orders = new List<OrderResult>();

            long sfStartOrderNumber = 0;
            long sfEndOrderNumber = 0;
            long sfLastOrderNumber = 0;
            if (!string.IsNullOrEmpty(startOrderNumber))
            {
                if (!long.TryParse(startOrderNumber, out sfStartOrderNumber))
                    throw new ApplicationException("Invalid start order number.");
            }
            if (!string.IsNullOrEmpty(endOrderNumber))
            {
                if (!long.TryParse(endOrderNumber, out sfEndOrderNumber))
                    throw new ApplicationException("Invalid end order number.");
            }
            if (!string.IsNullOrEmpty(pageKey))
            {
                if (!long.TryParse(pageKey, out sfLastOrderNumber))
                    throw new ApplicationException("Invalid paging key.");
            }

            int recordsCount;
            using (var sfOrderAccess = new COrderAccess())
            {
                var whereSql = "FROM " + sfOrderAccess.TableName + " WHERE (OrderNumber IS NOT NULL) AND (Void <> 1)";
                if (!string.IsNullOrEmpty(startOrderNumber))
                    whereSql += " AND (OrderNumber >= " + sfStartOrderNumber + ")";
                if (!string.IsNullOrEmpty(endOrderNumber))
                    whereSql += " AND (OrderNumber <= " + sfEndOrderNumber + ")";
                if (!string.IsNullOrEmpty(pageKey))
                    whereSql += " AND (OrderNumber > " + sfLastOrderNumber + ")";
                if (startOrderDate.HasValue)
                {
                    switch (StoreFrontConfiguration.DataBase)
                    {
                        case DatabaseType.Access:
                            whereSql += " AND (CDate(DateOrdered) >= #" + startOrderDate.Value.ToShortDateString() + "#)";
                            break;
                        case DatabaseType.SqlServer:
                            whereSql += " AND (CONVERT(DATETIME, DateOrdered) >= '" + startOrderDate.Value.ToShortDateString() + "')";
                            break;
                    }
                }
                if (endOrderDate.HasValue)
                {
                    switch (StoreFrontConfiguration.DataBase)
                    {
                        case DatabaseType.Access:
                            whereSql += " AND (CDate(DateOrdered) <= #" + endOrderDate.Value.ToShortDateString() + "#)";
                            break;
                        case DatabaseType.SqlServer:
                            whereSql += " AND (CONVERT(DATETIME, DateOrdered) <= '" + endOrderDate.Value.ToShortDateString() + "')";
                            break;
                    }
                }

                var selectSql = "SELECT TOP " + maxCount + " OrderNumber " + whereSql;
                using (var sfOrders = sfOrderAccess.SQLSelect(selectSql + " ORDER BY OrderNumber", null, 0, 0))
                {
                    foreach (DataRow row in sfOrders.Tables[0].Rows)
                    {
                        orders.Add(BuildOrderResult(Convert.ToInt64(row["OrderNumber"])));
                    }    
                }

                var countSql = "SELECT COUNT(*) " + whereSql;
                using (var sfCount = sfOrderAccess.SQLSelect(countSql, null, 0, 0))
                {
                    recordsCount = Convert.ToInt32(sfCount.Tables[0].Rows[0][0]);
                }
            }

            if (orders.Count == 0)
                return null;

            var result = new OrderResultsPage();
            result.Key = orders[orders.Count - 1].Order.OrderNumber;
            result.OrderResults = orders.ToArray();
            result.Remaining = recordsCount - orders.Count;
            return result;
        }

        /// <summary>
        /// Return one Shipment from the store.
        /// </summary>
        /// <param name="orderId">The OrderNumber of the StoreFront COrder object.</param>
        /// <param name="packageId">The ID of the corresponding OrderAddress object and a flag
        /// to indicate the order ("a") or backorder shipment ("b").</param>
        /// <returns>The Shipment.</returns>
        /// <remarks>StoreFront stores shipments in objects called OrderAddress. Each
        /// OrderAddress can contain 1 or 2 shipments: the order shipment and the
        /// backorder shipment.</remarks>
        protected static PackageResult GetPackageFromStore(string orderId, string packageId)
        {
            long sfOrderNumber;
            if (!long.TryParse(orderId, out sfOrderNumber))
                throw new ApplicationException("Invalid order ID.");

            bool sfBackorder;
            if (packageId.StartsWith("a", StringComparison.InvariantCultureIgnoreCase))
                sfBackorder = false;
            else if (packageId.StartsWith("b", StringComparison.InvariantCultureIgnoreCase))
                sfBackorder = true;
            else
                throw new ApplicationException("Invalid package ID.");
            long sfOrderAddressId;

            if (!long.TryParse(packageId.Substring(1), out sfOrderAddressId))
                throw new ApplicationException("Invalid package ID.");

            using (var sfOrder = new COrder(sfOrderNumber))
            {
                foreach (COrderAddress sfOrderAddress in sfOrder.OrderAddresses)
                {
                    if (sfOrderAddress.Address.ID == sfOrderAddressId)
                        return BuildPackageResult(sfOrder, sfOrderAddress, sfBackorder);
                }
            }

            throw new ApplicationException("Invalid order or shipment ID.");
        }

        protected static List<TrackingProvider> GetTrackingProvidersFromStore()
        {
            var trackingProviders = new List<TrackingProvider>();

            using (var management = new CShippingManagement())
            using (var carriers = management.getCarriers())
            {
                foreach (DataRow row in carriers.Tables[0].Rows)
                {
                    var name = ShippingAgentHelper.ConvertToString(row["Name"]);
                    if (string.IsNullOrEmpty(name)) continue;

                    var code = ShippingAgentHelper.ConvertToString(row["Code"]);
                    if (string.IsNullOrEmpty(code) || code.Equals("none", StringComparison.InvariantCultureIgnoreCase)) continue;

                    trackingProviders.Add(new TrackingProvider { Name = name, TrackingProviderId = code });
                }
            }

            return trackingProviders;
        }

        protected static UpdateResult UpdatePackageInStore(Update update)
        {
            long sfOrderNumber;
            if (!long.TryParse(update.OrderId, out sfOrderNumber))
                throw new ApplicationException("Invalid order ID.");

            bool sfBackorder;
            if (update.PackageId.StartsWith("a", StringComparison.InvariantCultureIgnoreCase))
                sfBackorder = false;
            else if (update.PackageId.StartsWith("b", StringComparison.InvariantCultureIgnoreCase))
                sfBackorder = true;
            else
                throw new ApplicationException("Invalid package ID.");
            long sfOrderAddressId;

            if (!long.TryParse(update.PackageId.Substring(1), out sfOrderAddressId))
                throw new ApplicationException("Invalid package ID.");

            using (var sfOrder = new COrder(sfOrderNumber))
            {
                foreach (COrderAddress sfOrderAddress in sfOrder.OrderAddresses)
                {
                    if (sfOrderAddress.Address.ID == sfOrderAddressId)
                        return BuildUpdateResult(update, sfOrder, sfOrderAddress, sfBackorder);
                }
            }
            throw new ApplicationException("Invalid order and shipment ID.");
        }

        #endregion

        private const string COD = "COD";
        private const string DISCOUNTS = "Discounts";
        private const string GIFT_CERTIFICATES = "Gift Certificates";
        private const string GIFTWRAPS = "Gift Wraps";
        private const string HANDLING = "Handling";
        private const string SHIPPING = "Shipping";
        private const string TAXES = "Taxes";

        private static bool AddressesAreEqual(COrderAddress sfOrderAddress, ShippingAddress shippingAddress)
        {
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.City, shippingAddress.City, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.Company, shippingAddress.Company, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.Country, shippingAddress.Country, StringComparison.InvariantCultureIgnoreCase)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.Fax, shippingAddress.Fax, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.FirstName, shippingAddress.FirstName, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.LastName, shippingAddress.LastName, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.Address1, shippingAddress.Line1, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.Address2, shippingAddress.Line2, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.Phone, shippingAddress.Phone, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.Zip, shippingAddress.PostalCode, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(sfOrderAddress.Address.State, shippingAddress.Region, StringComparison.InvariantCultureIgnoreCase)) return false;
            if (sfOrderAddress.Residential != shippingAddress.Residential) return false;
            return true;
        }

        private static Address BuildBillingAddress(COrder sfOrder)
        {
            var address = new Address();
            address.AddressId = sfOrder.BillAddress.ID.ToString();
            address.City = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.City);
            address.Company = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.Company);
            address.Country = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.Country);
            address.Email = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.EMail);
            address.Fax = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.Fax);
            address.FirstName = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.FirstName);
            address.LastName = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.LastName);
            address.Line1 = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.Address1);
            address.Line2 = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.Address2);
            address.Phone = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.Phone);
            address.PostalCode = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.Zip);
            address.Region = ShippingAgentHelper.StringOrNull(sfOrder.BillAddress.State);
            return address;
        }

        private static Customer BuildCustomer(COrder sfOrder)
        {
            var customer = new Customer();
            var sfCustomer = new StoreFront.SystemBase.Customer();
            new CCustomer().FillCustomer(ref sfCustomer, sfOrder.CustomerID);
            customer.CustomerId = sfOrder.CustomerID.ToString();
            customer.CustomerUrl = BuildCustomerUrl("CustProfileMain.aspx", "");
            customer.Email = sfCustomer.Email;
            customer.FirstName = sfCustomer.FirstName;
            customer.LastName = sfCustomer.LastName;
            customer.MerchantUrl = BuildMerchantUrl("management/Customers.aspx", "");
            customer.UserName = sfCustomer.Email;
            return customer;
        }

        private static string BuildCustomerUrl(string page, string query)
        {
            try
            {
                var url = new UriBuilder(StoreFrontConfiguration.SiteURL);
                url.Path += page;
                url.Query = query;
                return url.Uri.ToString();
            }
            catch
            {
                return string.Empty;
            }
        }

        private static string BuildDescription(COrderItem sfItem)
        {
            var descriptions = new List<string>();
            foreach (CAttribute attribute in sfItem.Attributes)
            {
                var description = new StringBuilder();
                description.Append(string.Concat(attribute.Name, ": "));
                var details = new List<string>();
                foreach (CAttributeDetail detail in attribute.AttributeDetails)
                {
                    if (attribute.AttributeType == tAttributeType.Normal)
                        details.Add(detail.Name);
                    else
                        details.Add(detail.Customor_Custom_Description);
                }
                description.Append(string.Join(" ", details.ToArray()));
                descriptions.Add(description.ToString());
            }
            foreach (CGiftWrap wrap in sfItem.GiftWraps)
            {
                descriptions.Add(string.Format("GIFT WRAP FROM: {0} TO: {1} MESSAGE: {2}", wrap.MessageFrom, wrap.MessageTo, wrap.Message));
            }
            return descriptions.Count > 0 ? string.Join(", ", descriptions.ToArray()) : null;
        }

        private static decimal BuildInsuranceValue(ArrayList sfItems, bool sfBackorder)
        {
            decimal value = 0;
            foreach (COrderItem sfItem in sfItems)
            {
                if (!sfItem.IsShipable) continue;

                var backorderQuantity = sfItem.SavedBOQty;
                var orderQuantity = sfItem.Quantity - backorderQuantity;

                if (sfBackorder && backorderQuantity <= 0) continue;
                if (!sfBackorder && orderQuantity <= 0) continue;

                var quantity = sfBackorder ? backorderQuantity : orderQuantity;
                value += quantity*sfItem.ItemPrice;
            }
            return value;
        }

        private static InventoryResult BuildInventoryResult(CReportDetails sfOrderSummary)
        {
            using (var sfOrder = new COrder(sfOrderSummary.OrderNumber))
            {
                if (sfOrder.OrderAddresses == null)
                    return null;

                var itemCounts = new List<ItemCount>();
                foreach (COrderAddress sfOrderAddress in sfOrder.OrderAddresses)
                {
                    foreach (COrderItem sfItem in sfOrderAddress.OrderItems)
                    {
                        if (sfItem.Inventory == null || !sfItem.Inventory.InventoryTracked)
                            continue;

                        var attributes = new CAttributes();
                        foreach (CAttribute attribute in sfItem.Attributes)
                            attributes.Add(attribute);

                        var itemCount = new ItemCount();
                        itemCount.ItemId = sfItem.OrderItemID.ToString();
                        itemCount.Count = sfItem.Inventory.GetQuantity(attributes);
                        itemCounts.Add(itemCount);
                    }
                }
                return new InventoryResult { Success = true, ItemCounts = itemCounts.Count > 0 ? itemCounts.ToArray() : null };
            }
        }

        private static Supplier BuildManufacturer(IProduct sfItem)
        {
            // StoreFront does not fill in the manufacturer
            try
            {
                using (var product = new CProductManagement(Convert.ToInt32(sfItem.ProductID)))
                using (var manufacturer = new CManufacturer(product.Manufacturer))
                {
                    if (manufacturer.ManufacturerAddress.Company.Equals("no manufacturer", StringComparison.CurrentCultureIgnoreCase))
                        return null;

                    var supplier = new Supplier();
                    supplier.Email = ShippingAgentHelper.StringOrNull(manufacturer.ManufacturerAddress.EMail);
                    supplier.Name = manufacturer.ManufacturerAddress.Company;
                    supplier.SupplierId = product.Manufacturer.ToString();
                    return supplier;
                }
            }
            catch (IndexOutOfRangeException)
            {
                // Product not in database
                return null;
            }
        }

        private static string BuildMerchantUrl(string page, string query)
        {
            try
            {
                var url = new UriBuilder(StoreFrontConfiguration.SSLPath);
                url.Path += page;
                url.Query = query;
                return url.Uri.ToString();
            }
            catch
            {
                return string.Empty;
            }
        }

        private static NamedAmount[] BuildOrderCharges(ITotalTax sfOrder)
        {
            var charges = new NamedAmountList();
            if (sfOrder.CODTotal != 0) charges[COD].Amount += sfOrder.CODTotal;
            if (sfOrder.CountryTax != 0) charges[TAXES].Amount += sfOrder.CountryTax;
            if (sfOrder.HandlingTotal != 0) charges[HANDLING].Amount += sfOrder.HandlingTotal;
            if (sfOrder.LocalTax != 0) charges[TAXES].Amount += sfOrder.LocalTax;
            if (sfOrder.ShippingTotal != 0) charges[SHIPPING].Amount += sfOrder.ShippingTotal;
            if (sfOrder.StateTax != 0) charges[TAXES].Amount += sfOrder.StateTax;
            return charges.Count > 0 ? charges.ToArray() : null;
        }

        private static Item[] BuildOrderItems(COrder sfOrder)
        {
            var items = new List<Item>();
            foreach (COrderAddress sfOrderAddress in sfOrder.OrderAddresses)
            {
                var shipmentItems = BuildPackageItems(sfOrderAddress.OrderItems, false);
                if (shipmentItems != null)
                    items.AddRange(shipmentItems);
                shipmentItems = BuildPackageItems(sfOrderAddress.OrderItems, true);
                if (shipmentItems == null)
                    continue;
                for (var index = 0; index < shipmentItems.Length; index++)
                {
                    var shipmentItem = shipmentItems[index];
                    var item = items.Find(i => i.ItemId == shipmentItem.ItemId);
                    if (item == null)
                        items.Add(shipmentItem);
                    else
                    {
                        item.Quantity += shipmentItem.Quantity;
                        item.LineTotal += shipmentItem.LineTotal;
                    }
                }
            }
            return items.Count > 0 ? items.ToArray() : null;
        }

        private static NamedAmount[] BuildOrderPayments(COrder sfOrder)
        {
            var payments = new NamedAmountList();
            if (sfOrder.GiftCertificateTotal != 0) payments[GIFT_CERTIFICATES].Amount += sfOrder.GiftCertificateTotal;
            if (sfOrder.DiscountTotal != 0) payments[DISCOUNTS].Amount += sfOrder.DiscountTotal;
            if (!sfOrder.PaymentsPending)
            {
                var payment = new NamedAmount();
                payment.Amount = sfOrder.TotalBilledAmt;
                payment.Description = BuildPaymentDescription(sfOrder);
                payment.Name = "Order Payment";
                payments.Add(payment);
            }
            if (sfOrder.BackOrderTotalAmt > 0 && !sfOrder.BOPaymentsPending)
            {
                var payment = new NamedAmount();
                payment.Amount = sfOrder.BackOrderTotalAmt;
                payment.Description = BuildPaymentDescription(sfOrder);
                payment.Name = "Backorder Payment";
                payments.Add(payment);
            }
            return payments.Count > 0 ? payments.ToArray() : null;
        }

        private static OrderResult BuildOrderResult(long sfOrderNumber)
        {
            using (var management = new CManagement(new sfSearchContainer { OrderNumber = sfOrderNumber }))
            {
                var summaries = management.OrderSummary;
                if (summaries.Count == 0)
                    throw new ApplicationException("Invalid order ID.");

                COrderManagmentSummary sfOrderSummary = (COrderManagmentSummary)summaries[0];
                var orderResult = BuildOrderResultOnly(sfOrderSummary);
                if (orderResult.Success && orderResult.Order != null)
                {
                    try
                    {
                        using (var sfOrder = new COrder(sfOrderSummary.OrderNumber))
                        {
                            orderResult.Order.Packages = BuildPackages(sfOrder);
                        }
                    }
                    catch (ApplicationException ex)
                    {
                        orderResult.Success = false;
                        orderResult.Message = ex.Message;
                    }
                    catch (Exception ex)
                    {
                        orderResult.Success = false;
                        orderResult.Message = ex.ToString();
                    }
                }
                return orderResult;
            }
        }

        private static OrderResult BuildOrderResultOnly(long sfOrderNumber)
        {
            using (var management = new CManagement(new sfSearchContainer { OrderNumber = sfOrderNumber }))
            {
                var summaries = management.OrderSummary;
                if (summaries.Count == 0)
                    throw new ApplicationException("Invalid order ID.");

                return BuildOrderResultOnly((COrderManagmentSummary)summaries[0]);
            }
        }

        private static OrderResult BuildOrderResultOnly(COrderManagmentSummary sfOrderSummary)
        {
            var order = new Order();
            order.CustomerUrl = BuildCustomerUrl("OrderDetail.aspx", "OrderID=" + sfOrderSummary.uid);
            order.MerchantUrl = BuildMerchantUrl("Management/OrderStatus.aspx", "OrderID=" + sfOrderSummary.OrderNumber);
            var orderDate = ShippingAgentHelper.ConvertToNullableDateTime(sfOrderSummary.OrderDate);
            order.OrderDate = orderDate.HasValue ? orderDate.Value : DateTime.MinValue;
            order.OrderId = sfOrderSummary.OrderNumber.ToString();
            order.OrderNumber = sfOrderSummary.OrderNumber.ToString();
            order.OrderStatus = ShippingAgentHelper.StringOrNull(ShippingAgentHelper.RemoveHtmlTags(sfOrderSummary.Status));

            try
            {
                using (var sfOrder = new COrder(sfOrderSummary.OrderNumber))
                {
                    order.OrderTotal = Convert.ToDecimal(sfOrder.GrandTotal);
                    order.BillingAddress = BuildBillingAddress(sfOrder);
                    order.Charges = BuildOrderCharges(sfOrder);
                    order.Customer = BuildCustomer(sfOrder);
                    order.Items = BuildOrderItems(sfOrder);
                    order.Payments = BuildOrderPayments(sfOrder);
                    order.PaymentStatus = BuildPaymentStatus(sfOrder);
                    order.ShippingStatus = BuildShippingStatus(sfOrder);
                    return new OrderResult { Success = true, Order = order };
                }
            }
            catch (ApplicationException ex)
            {
                return new OrderResult { Success = false, Message = ex.Message, Order = order };
            }
            catch (Exception ex)
            {
                return new OrderResult { Success = false, Message = ex.ToString(), Order = order };
            }
        }

        private static NamedAmount[] BuildPackageCharges(COrderAddress sfOrderAddress, bool sfBackorder)
        {
            var charges = new NamedAmountList();
            if (sfBackorder)
            {
                if (sfOrderAddress.BackOrderCountryTaxTotal != 0) charges[TAXES].Amount += sfOrderAddress.BackOrderCountryTaxTotal;
                if (sfOrderAddress.BackOrderHandlingTotal != 0) charges[HANDLING].Amount += sfOrderAddress.BackOrderHandlingTotal;
                if (sfOrderAddress.BackOrderLocalTaxTotal != 0) charges[TAXES].Amount += sfOrderAddress.BackOrderLocalTaxTotal;
                if (sfOrderAddress.BackOrderStateTaxTotal != 0) charges[TAXES].Amount += sfOrderAddress.BackOrderStateTaxTotal;
            }
            else
            {
                if (sfOrderAddress.NonBackOrderCountryTaxTotal != 0) charges[TAXES].Amount += sfOrderAddress.NonBackOrderCountryTaxTotal;
                if (sfOrderAddress.NonBackOrderHandlingTotal != 0) charges[HANDLING].Amount += sfOrderAddress.NonBackOrderHandlingTotal;
                if (sfOrderAddress.NonBackOrderLocalTaxTotal != 0) charges[TAXES].Amount += sfOrderAddress.NonBackOrderLocalTaxTotal;
                if (sfOrderAddress.NonBackOrderStateTaxTotal != 0) charges[TAXES].Amount += sfOrderAddress.NonBackOrderStateTaxTotal;
                if (sfOrderAddress.ShippingTotal != 0) charges[SHIPPING].Amount += sfOrderAddress.ShippingTotal;
                if (sfOrderAddress.DiscountTotal != 0) charges[DISCOUNTS].Amount += sfOrderAddress.DiscountTotal;
                // Move gift wrap charges from line item total to shipment charges
                foreach (COrderItem item in sfOrderAddress.OrderItems)
                {
                    if (item.GiftWrapPrice != 0) charges[GIFTWRAPS].Amount += item.GiftWrapPrice;
                }
            }
            return charges.Count > 0 ? charges.ToArray() : null;
        }

        private static Item[] BuildPackageItems(ArrayList sfItems, bool sfBackorder)
        {
            var items = new List<Item>();
            foreach (COrderItem sfItem in sfItems)
            {
                var backorderQuantity = sfItem.SavedBOQty;
                var orderQuantity = sfItem.Quantity - backorderQuantity;

                if (sfBackorder && backorderQuantity <= 0) continue;
                if (!sfBackorder && orderQuantity <= 0) continue;

                var item = new Item();
                item.Code = sfItem.ProductCode;
                item.Description = BuildDescription(sfItem);
                if (sfItem.Inventory != null && sfItem.Inventory.InventoryTracked)
                {
                    var attributes = new CAttributes();
                    foreach (CAttribute attribute in sfItem.Attributes)
                        attributes.Add(attribute);
                    item.Inventory = sfItem.Inventory.GetQuantity(attributes);
                }
                item.ItemId = sfItem.OrderItemID.ToString();
                item.LineTotal = sfBackorder ? sfItem.BackOrderTotal : sfItem.ItemTotal - sfItem.BackOrderTotal - sfItem.GiftWrapPrice;
                item.Manufacturer = BuildManufacturer(sfItem);
                item.Name = sfItem.Name;
                item.Price = sfItem.ItemPrice;
                item.Quantity = sfBackorder ? backorderQuantity : orderQuantity;
                item.Shippable = sfItem.IsShipable;
                item.Vendor = BuildVendor(sfItem);
                item.Weight = Convert.ToDouble(sfItem.Weight);
                items.Add(item);
            }
            return items.Count > 0 ? items.ToArray() : null;
        }

        private static PackageResult BuildPackageResult(COrder sfOrder, COrderAddress sfOrderAddress, bool sfBackorder)
        {
            if (sfBackorder && !sfOrderAddress.HasBackOrderShippableProducts)
                return new PackageResult { Success = true };
            if (!sfBackorder && !sfOrderAddress.HasNonBackOrderShippableProducts)
                return new PackageResult { Success = true };

            var package = new Package();
            package.CustomerUrl = BuildCustomerUrl("OrderTracking.aspx", "OrderID=" + sfOrder.UID);
            package.MerchantUrl = BuildMerchantUrl("management/TrackShipment.aspx",
                                                    string.Format("OrderID={0}&OrderAddressID={1}", sfOrder.UID, sfOrderAddress.Address.ID));
            package.OrderId = sfOrder.OrderNumber.ToString();
            package.PackageId = (sfBackorder ? "b" : "a") + sfOrderAddress.Address.ID;

            try
            {
                package.Charges = BuildPackageCharges(sfOrderAddress, sfBackorder);
                package.Destination = BuildShippingAddress(sfOrderAddress.Address, sfOrderAddress.Residential);
                package.Instructions = ShippingAgentHelper.StringOrNull(sfOrderAddress.Address.Instructions);
                package.InsuranceValue = BuildInsuranceValue(sfOrderAddress.OrderItems, sfBackorder);
                package.Items = BuildPackageItems(sfOrderAddress.OrderItems, sfBackorder);
                package.RateOptionName = sfOrderAddress.Address.ShipMethod;
                package.RateProviderName = BuildRateProviderName(sfOrderAddress.Address.ShipCarrierCode);
                var shipDate = BuildShipDate(sfOrder, sfOrderAddress, sfBackorder);
                if (shipDate.HasValue)
                {
                    package.ShipDate = shipDate.Value;
                    package.ShipDateSpecified = true;
                }
                else
                {
                    package.ShipDateSpecified = false; 
                }
                package.Shipped = BuildShipped(sfOrder, sfOrderAddress, sfBackorder);
                package.TrackingNumber = BuildTrackingNumber(sfOrderAddress, sfBackorder);
                package.TrackingProviderId = ShippingAgentHelper.StringOrNull(sfOrderAddress.Address.ShipCarrierCode);
                package.TrackingProviderName = package.RateProviderName;
                package.Warehouse = BuildWarehouse();
                package.Weight = BuildPackageWeight(sfOrderAddress.OrderItems, sfBackorder);
                return new PackageResult { Success = true, Package = package };
            }
            catch (ApplicationException ex)
            {
                return new PackageResult { Success = false, Message = ex.Message, Package = package };
            }
            catch (Exception ex)
            {
                return new PackageResult { Success = false, Message = ex.ToString(), Package = package };
            }
        }

        private static PackageResult[] BuildPackages(COrder sfOrder)
        {
            var packages = new List<PackageResult>();
            foreach (COrderAddress sfOrderAddress in sfOrder.OrderAddresses)
            {
                if (sfOrderAddress.HasNonBackOrderShippableProducts)
                    packages.Add(BuildPackageResult(sfOrder, sfOrderAddress, false));
                if (sfOrderAddress.HasBackOrderShippableProducts)
                    packages.Add(BuildPackageResult(sfOrder, sfOrderAddress, true));
            }
            return packages.Count > 0 ? packages.ToArray() : null;
        }

        private static double BuildPackageWeight(ArrayList sfItems, bool sfBackorder)
        {
            decimal weight = 0;
            foreach (COrderItem sfItem in sfItems)
            {
                var backorderQuantity = sfItem.SavedBOQty;
                var orderQuantity = sfItem.Quantity - backorderQuantity;

                if (sfBackorder && backorderQuantity <= 0) continue;
                if (!sfBackorder && orderQuantity <= 0) continue;

                var itemWeight = sfItem.Weight + sfItem.AttributesWeight;
                weight += (sfBackorder ? backorderQuantity : orderQuantity) * itemWeight;
            }
            return Convert.ToDouble(weight);
        }

        private static string BuildPaymentDescription(COrder sfOrder)
        {
            var methodName = StoreFrontConfiguration.PaymentMethodAccess.GetPaymentMethodName(sfOrder.PaymentMethod);
            if (methodName.Equals("echeck", StringComparison.InvariantCultureIgnoreCase))
                return methodName;
            if (methodName.Equals("credit card", StringComparison.InvariantCultureIgnoreCase))
                return string.Concat(sfOrder.OrderPayment.CardType, " -", GetCreditCardNumber(sfOrder.OrderPayment));
            if (methodName.Equals("po", StringComparison.InvariantCultureIgnoreCase))
                return string.Concat("Purchase Order #", sfOrder.OrderPayment.PONumber);
            if (methodName.Equals("phonefax", StringComparison.InvariantCultureIgnoreCase))
                return "Phone/Fax";
            if (methodName.Equals("cod", StringComparison.InvariantCultureIgnoreCase))
                return "COD";
            return string.Empty;
        }

        private static string BuildShippingStatus(COrder sfOrder)
        {

            var totalShipments = 0;
            foreach (COrderAddress address in sfOrder.OrderAddresses)
            {
                if (address.HasNonBackOrderShippableProducts)
                    totalShipments++;
                if (address.HasBackOrderShippableProducts)
                    totalShipments++;
            }
            if (totalShipments == 0)
                return "Non Shipping";

            var pendingShipments = 0;
            using (var management = new CManagement(sfOrder.UID))
            {
                foreach (COrderShipmentStatus status in management.get_OrderShipStatus(sfOrder.UID))
                {
                    if (status.HasShipmentPending)
                        pendingShipments++;
                    if (status.HasBOShipmentPending)
                        pendingShipments++;
                }
            }
            if (pendingShipments == 0)
                return "Shipped";

            return pendingShipments == totalShipments ? "Unshipped" : "Partially Shipped";
        }

        private static string GetCreditCardNumber(COrderPayment sfOrderPayment)
        {
            if (!string.IsNullOrEmpty(sfOrderPayment.LastFourDigits))
                return sfOrderPayment.LastFourDigits;
            if (!string.IsNullOrEmpty(sfOrderPayment.CreditCardNumber))
            {
                var number = GetDecryptedValue(sfOrderPayment.CreditCardNumber);
                return number.Length > 4 ? number.Substring(number.Length - 4) : number;
            }
            return string.Empty;
        }

        private static string GetDecryptedValue(string value)
        {
            if (StoreFrontConfiguration.ConvertedFrom3DES)
                return string.Empty;
            var values = value.Split(' ');
            if (values.Length < 17)
                return string.Empty;

            try
            {
                var key = new byte[8];
                var iv = new byte[8];
                for (int index = 0; index < 8; index++)
                {
                    key[index] = Convert.ToByte(values[index]);
                    iv[index] = Convert.ToByte(values[index + 8]);
                }
                var encrypted = new byte[values.Length - 16];
                for (int index = 0; index <= encrypted.GetUpperBound(0); index++)
                    encrypted[index] = Convert.ToByte(values[index + 16]);

                using (var crypto = new CStoreFrontCrypto2(encrypted))
                {
                    crypto.Type = CryptoType.Decrypt;
                    return crypto.GetData(key, iv);
                }
            }
            catch
            {
                return string.Empty;
            }
        }

        private static string BuildPaymentStatus(COrder sfOrder)
        {
            if (sfOrder.BackOrderTotalAmt > 0)
            {
                if (sfOrder.PaymentsPending && sfOrder.BOPaymentsPending)
                    return "Unpaid";
                if (!sfOrder.PaymentsPending && !sfOrder.BOPaymentsPending)
                    return "Paid";
                return "Partially Paid";
            }
            return sfOrder.PaymentsPending ? "Unpaid" : "Paid";
        }

        private static string BuildRateProviderName(string carrierCode)
        {
            if (string.IsNullOrEmpty(carrierCode) || carrierCode.Equals("NONE", StringComparison.InvariantCultureIgnoreCase))
                return null;

            using (var management = new CShippingManagement())
            using (var carriers = management.getCarriers())
            {
                foreach (DataRow row in carriers.Tables[0].Rows)
                {
                    if (ShippingAgentHelper.ConvertToString(row["Code"]).Equals(carrierCode, StringComparison.CurrentCultureIgnoreCase))
                    {
                        return ShippingAgentHelper.ConvertToString(row["Name"]);
                    }
                }
            }
            return carrierCode;
        }

        private static DateTime? BuildShipDate(COrder sfOrder, COrderAddress sfOrderAddress, bool sfBackorder)
        {
            using (var management = new CManagement(sfOrder.UID))
            {
                foreach (COrderShipmentStatus status in management.get_OrderShipStatus(sfOrder.UID))
                {
                    if (status.AddressID != sfOrderAddress.Address.ID)
                        continue;
                    var sentdate = management.get_Tracking(sfOrderAddress.Address.ID, sfBackorder ? 1 : 0).SentDate;
                    if (string.IsNullOrEmpty(sentdate))
                        return null;
                    DateTime outdate;
                    if (DateTime.TryParse(sentdate, out outdate))
                        return outdate;
                    return null;
                }
            }
            return null;
        }

        private static bool BuildShipped(COrder sfOrder, COrderAddress sfOrderAddress, bool sfBackorder)
        {
            using (var management = new CManagement(sfOrder.UID))
            {
                foreach (COrderShipmentStatus status in management.get_OrderShipStatus(sfOrder.UID))
                {
                    if (status.AddressID != sfOrderAddress.Address.ID)
                        continue;
                    if (sfBackorder && !status.HasBOShipmentPending)
                        return true;
                    if (!sfBackorder && !status.HasShipmentPending)
                        return true;
                }
            }
            return false;
        }

        private static ShippingAddress BuildShippingAddress(StoreFront.SystemBase.Address sfAddress, bool residential)
        {
            var shippingAddress = new ShippingAddress();
            shippingAddress.AddressId = sfAddress.ID.ToString();
            shippingAddress.City = ShippingAgentHelper.StringOrNull(sfAddress.City);
            shippingAddress.Company = ShippingAgentHelper.StringOrNull(sfAddress.Company);
            shippingAddress.Country = ShippingAgentHelper.StringOrNull(sfAddress.Country);
            shippingAddress.Email = ShippingAgentHelper.StringOrNull(sfAddress.EMail);
            shippingAddress.Fax = ShippingAgentHelper.StringOrNull(sfAddress.Fax);
            shippingAddress.FirstName = ShippingAgentHelper.StringOrNull(sfAddress.FirstName);
            shippingAddress.LastName = ShippingAgentHelper.StringOrNull(sfAddress.LastName);
            shippingAddress.Line1 = ShippingAgentHelper.StringOrNull(sfAddress.Address1);
            shippingAddress.Line2 = ShippingAgentHelper.StringOrNull(sfAddress.Address2);
            shippingAddress.Phone = ShippingAgentHelper.StringOrNull(sfAddress.Phone);
            shippingAddress.PostalCode = ShippingAgentHelper.StringOrNull(sfAddress.Zip);
            shippingAddress.Region = ShippingAgentHelper.StringOrNull(sfAddress.State);
            shippingAddress.Residential = residential;
            return shippingAddress;
        }

        private static string BuildTrackingNumber(COrderAddress sfOrderAddress, bool sfBackorder)
        {
            using (var access = new COrderTrackingAccess())
            using (var tracking = access.GetTracking(sfOrderAddress.Address.ID))
            {
                if (tracking.Tables.Count == 0)
                    throw new ApplicationException("Invalid package ID.");

                var sfTrack = tracking.Tables[0].Select("(VendorName='' OR VendorName IS NULL) AND BackOrderFlag = " + (sfBackorder ? 1 : 0));
                foreach (var row in sfTrack)
                {
                    return row.IsNull("TrackingNumber") ? null : row["TrackingNumber"].ToString();
                }
            }
            return null;
        }

        private static UpdateResult BuildUpdateResult(Update update, COrder sfOrder, COrderAddress sfOrderAddress, bool sfBackorder)
        {
            try
            {
                var notes = new List<string>();

                if (update.Destination != null)
                {
                    if (!AddressesAreEqual(sfOrderAddress, update.Destination))
                    {
                        using (var access = new COrderAddressAccess())
                        using (var addresses = access.GetOrderAddress(sfOrder.UID))
                        {
                            if (addresses.Tables.Count == 0)
                                throw new ApplicationException("Invalid order ID.");

                            var sfAddress = addresses.Tables[0].Select("UID=" + sfOrderAddress.Address.ID);
                            if (sfAddress.Length == 0)
                                throw new ApplicationException("Invalid package ID.");

                            sfAddress[0]["Address1"] = update.Destination.Line1;
                            sfAddress[0]["Address2"] = update.Destination.Line2;
                            sfAddress[0]["City"] = update.Destination.City;
                            sfAddress[0]["Company"] = update.Destination.Company;
                            sfAddress[0]["Country"] = update.Destination.Country;
                            sfAddress[0]["Fax"] = update.Destination.Fax;
                            sfAddress[0]["FirstName"] = update.Destination.FirstName;
                            sfAddress[0]["LastName"] = update.Destination.LastName;
                            sfAddress[0]["Phone"] = update.Destination.Phone;
                            sfAddress[0]["State"] = update.Destination.Region;
                            sfAddress[0]["Zip"] = update.Destination.PostalCode;
                            sfAddress[0]["Email"] = update.Destination.Email;
                            access.Update(addresses, access.TableName);
                        }
                        notes.Add("Changed shipping address.");
                    }
                }

                if (update.TrackingNumber != null)
                {
                    var trackingNumber = BuildTrackingNumber(sfOrderAddress, sfBackorder) ?? string.Empty;
                    if (!trackingNumber.Equals(update.TrackingNumber, StringComparison.InvariantCultureIgnoreCase))
                    {
                        using (var access = new COrderTrackingAccess())
                        using (var tracking = access.GetTracking(sfOrderAddress.Address.ID))
                        {
                            if (tracking.Tables.Count == 0)
                                throw new ApplicationException("Invalid package ID.");

                            var sfTrack = tracking.Tables[0].Select("(VendorName='' OR VendorName IS NULL) AND BackOrderFlag=" + (sfBackorder ? 1 : 0));
                            if (sfTrack.Length == 0)
                            {
                                sfTrack = new[] { tracking.Tables[0].NewRow() };
                                sfTrack[0]["BackOrderFlag"] = sfBackorder;
                                sfTrack[0]["ShipToAddressID"] = sfOrderAddress.Address.ID;
                                sfTrack[0]["VendorName"] = string.Empty;
                                tracking.Tables[0].Rows.Add(sfTrack[0]);
                            }
                            sfTrack[0]["TrackingNumber"] = update.TrackingNumber;
                            access.Update(tracking, access.TableName);
                            notes.Add(string.Format("Changed shipment tracking number to {0}.", update.TrackingNumber));
                        }
                    }
                }

                if (update.TrackingProviderId != null)
                {
                    if (!sfOrderAddress.Address.ShipCarrierCode.Equals(update.TrackingProviderId, StringComparison.InvariantCultureIgnoreCase))
                    {
                        using (var access = new COrderAddressAccess())
                        using (var addresses = access.GetOrderAddress(sfOrder.UID))
                        {
                            if (addresses.Tables.Count == 0)
                                throw new ApplicationException("Invalid order ID.");

                            var sfAddress = addresses.Tables[0].Select("UID=" + sfOrderAddress.Address.ID);
                            if (sfAddress.Length == 0)
                                throw new ApplicationException("Invalid package ID.");

                            sfAddress[0]["ShipCarrierCode"] = update.TrackingProviderId;
                            access.Update(addresses, access.TableName);
                        }
                        notes.Add(string.Format("Changed shipment tracking provider code to {0}.", update.TrackingProviderId));
                    }
                }

                if (update.ShipDateSpecified)
                {
                    var shipdate = BuildShipDate(sfOrder, sfOrderAddress, sfBackorder);
                    if (!shipdate.HasValue || update.ShipDate != shipdate.Value)
                    {
                        using (var access = new COrderAddressAccess())
                        using (var addresses = access.GetOrderAddress(sfOrder.UID))
                        {
                            if (addresses.Tables.Count == 0)
                                throw new ApplicationException("Invalid order ID.");

                            var sfAddress = addresses.Tables[0].Select("UID=" + sfOrderAddress.Address.ID);
                            if (sfAddress.Length == 0)
                                throw new ApplicationException("Invalid package ID.");

                            if (sfBackorder)
                                sfAddress[0]["BOPending"] = 0;
                            else
                                sfAddress[0]["Pending"] = 0;
                            access.Update(addresses, access.TableName);
                        }

                        using (var access = new COrderTrackingAccess())
                        using (var tracking = access.GetTracking(sfOrderAddress.Address.ID))
                        {
                            if (tracking.Tables.Count == 0)
                                throw new ApplicationException("Invalid package ID.");

                            var sfTrack = tracking.Tables[0].Select("(VendorName='' OR VendorName IS NULL) AND BackOrderFlag=" + (sfBackorder ? 1 : 0));
                            if (sfTrack.Length == 0)
                            {
                                sfTrack = new[] { tracking.Tables[0].NewRow() };
                                sfTrack[0]["BackOrderFlag"] = sfBackorder;
                                sfTrack[0]["ShipToAddressID"] = sfOrderAddress.Address.ID;
                                sfTrack[0]["VendorName"] = string.Empty;
                                tracking.Tables[0].Rows.Add(sfTrack[0]);
                            }
                            sfTrack[0]["SentDate"] = update.ShipDate.ToString("M/d/yyyy");
                            access.Update(tracking, access.TableName);
                        }
                        notes.Add(string.Format("Changed shipping date to {0}.", update.ShipDate.ToString("M/d/yyyy")));
                    }
                }

                if (notes.Count > 0)
                {
                    var result = new UpdateResult();
                    result.OrderResult = BuildOrderResultOnly(sfOrder.OrderNumber);
                    result.PackageResult = BuildPackageResult(sfOrder, sfOrderAddress, sfBackorder);
                    result.Message = string.Join(" ", notes.ToArray());
                    result.Success = true;
                    return result;
                }

                return new UpdateResult { Success = true, Message = "No change." };
            }
            catch (Exception ex)
            {
                return new UpdateResult { Success = false, Message = ex.Message };
            }
        }

        private static Supplier BuildVendor(IProduct sfItem)
        {
            try
            {
                using (var product = new CProductManagement(Convert.ToInt32(sfItem.ProductID)))
                using (var vendor = new CVendor(product.Vendor))
                {
                    if (vendor.VendorAddress.Company.Equals("no vendor", StringComparison.CurrentCultureIgnoreCase))
                        return null;

                    var supplier = new Supplier();
                    supplier.Email = ShippingAgentHelper.StringOrNull(vendor.VendorAddress.EMail);
                    supplier.Name = vendor.VendorAddress.Company;
                    supplier.SupplierId = product.Vendor.ToString();
                    return supplier;
                }
            }
            catch (IndexOutOfRangeException)
            {
                // Product not in database
                return null;
            }
        }

        private static Address BuildWarehouse()
        {
            using (var management = new CShippingManagement())
            {
                var address = new Address();
                address.AddressId = "Origin";
                address.City = ShippingAgentHelper.StringOrNull(management.AdminShipping.OriginCity);
                address.Company = ShippingAgentHelper.StringOrNull(management.AdminShipping.StoreName);
                address.Country = ShippingAgentHelper.StringOrNull(management.AdminShipping.OriginCountry);
                address.Email = ShippingAgentHelper.StringOrNull(management.AdminShipping.PrimaryEmail);
                address.PostalCode = ShippingAgentHelper.StringOrNull(management.AdminShipping.OriginZip);
                address.Region = ShippingAgentHelper.StringOrNull(management.AdminShipping.OriginState);
                return address;
            }
        }
    }
}
